<html>
 <head>
  <meta charset="utf-8"/>
  <meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport"/>
  <title>
   主题：如何connect R to database然后计算?  | 数螺 | NAUT IDEA
  </title>
  <link href="http://cdn.bootcss.com/bootstrap/3.3.6/css/bootstrap-theme.min.css" rel="stylesheet"/>
  <link href="http://cdn.bootcss.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet"/>
  <style type="text/css">
   #xmain img {
                  max-width: 100%;
                  display: block;
                  margin-top: 10px;
                  margin-bottom: 10px;
                }

                #xmain p {
                    line-height:150%;
                    font-size: 16px;
                    margin-top: 20px;
                }

                #xmain h2 {
                    font-size: 24px;
                }

                #xmain h3 {
                    font-size: 20px;
                }

                #xmain h4 {
                    font-size: 18px;
                }


                .header {
	           background-color: #0099ff;
	           color: #ffffff;
	           margin-bottom: 20px;
	        }

	        .header p {
                  margin: 0px;
                  padding: 10px 0;
                  display: inline-block;  
                  vertical-align: middle;
                  font-size: 16px;
               }

               .header a {
                 color: white;
               }

              .header img {
                 height: 25px;
              }
  </style>
  <script src="http://cdn.bootcss.com/jquery/3.0.0/jquery.min.js">
  </script>
  <script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML" type="text/javascript">
   MathJax.Hub.Config({elements: ["bbpress-forums"]});
  </script>
  <script src="http://nautstatic-10007657.file.myqcloud.com/static/css/readability.min.js" type="text/javascript">
  </script>
  <script type="text/javascript">
   $(document).ready(function() {
                 var loc = document.location;
                 var uri = {
                  spec: "http://cos.name/cn/topic/11207/",
                  host: "http://cos.name",
                  prePath: "http://cos.name",
                  scheme: "http",
                  pathBase: "http://cos.name/"
                 };
    
                 var documentClone = document.cloneNode(true);
                 var article = new Readability(uri, documentClone).parse();
     
                 document.getElementById("xmain").innerHTML = article.content;
                });
  </script>
  <!-- 1466467296: Accept with keywords: (title(0.333333333333):主题,计算,论坛, topn(0.233333333333):帖子,专门,会员,事件,小时,用户名,数据库,计算,速度,数据,处理结果,比方说,收件人,时间,C++,获取数据,普通,股价,发件人,优化,效率,语句,版主,代码,主题,是不是,算法,收盘,时候,讨论区).-->
 </head>
 <body class="topic bbpress single single-topic postid-11207 single-author sidebar" onload="">
  <div class="header">
   <div class="container">
    <div class="row">
     <div class="col-xs-6 col-sm-6 text-left">
      <a href="/databee">
       <img src="http://nautidea-10007657.cos.myqcloud.com/logo_white.png"/>
      </a>
      <a href="/databee">
       <p>
        数螺
       </p>
      </a>
     </div>
     <div class="hidden-xs col-sm-6 text-right">
      <p>
       致力于数据科学的推广和知识传播
      </p>
     </div>
    </div>
   </div>
  </div>
  <div class="container text-center">
   <h1>
    主题：如何connect R to database然后计算?
   </h1>
  </div>
  <div class="container" id="xmain">
   <div class="hfeed site" id="page">
    <header class="site-header" id="masthead" role="banner">
     <div id="cos-logo">
      <a href="http://cos.name/cn">
       <img src="http://cos.name/cn/wp-content/themes/COS-forest/images/headers/cos-logo.png"/>
      </a>
     </div>
     <div class="navbar" id="navbar">
      <nav class="navigation main-navigation" id="site-navigation" role="navigation">
       <h3 class="menu-toggle">
        菜单
       </h3>
       <div class="menu-%e8%8f%9c%e5%8d%951-container">
        <ul class="nav-menu" id="menu-%e8%8f%9c%e5%8d%951">
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-407772" id="menu-item-407772">
          <a href="http://cos.name/cn/">
           论坛首页
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-407773" id="menu-item-407773">
          <a href="http://cos.name/cn/forums/">
           讨论区
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-407774" id="menu-item-407774">
          <a href="http://cos.name/cn/wp-login.php?action=register">
           注册
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-407819" id="menu-item-407819">
          <a href="http://cos.name/">
           主站
          </a>
         </li>
        </ul>
       </div>
      </nav>
      <!-- #site-navigation -->
     </div>
     <!-- #navbar -->
    </header>
    <!-- #masthead -->
    <div class="site-main" id="main">
     <div class="content-area" id="primary">
      <div class="site-content" id="content" role="main">
       <article class="post-11207 topic type-topic status-publish hentry" id="post-11207">
        <header class="entry-header">
         <h1 class="entry-title">
          如何connect R to database然后计算?
         </h1>
        </header>
        <!-- .entry-header -->
        <div class="entry-content">
         <div id="bbpress-forums">
          <div class="bbp-breadcrumb">
           <p>
            <a class="bbp-breadcrumb-home" href="http://cos.name/cn/">
             COS论坛 | 统计之都
            </a>
            <span class="bbp-breadcrumb-sep">
             ›
            </span>
            <a class="bbp-breadcrumb-root" href="http://cos.name/cn/forums/">
             讨论区
            </a>
            <span class="bbp-breadcrumb-sep">
             ›
            </span>
            <a class="bbp-breadcrumb-forum" href="http://cos.name/cn/forum/software/">
             软件应用
            </a>
            <span class="bbp-breadcrumb-sep">
             ›
            </span>
            <a class="bbp-breadcrumb-forum" href="http://cos.name/cn/forum/software/r-language/">
             S-Plus &amp; R语言
            </a>
            <span class="bbp-breadcrumb-sep">
             ›
            </span>
            <span class="bbp-breadcrumb-current">
             如何connect R to database然后计算?
            </span>
           </p>
          </div>
          <div class="bbp-template-notice info">
           <p class="bbp-topic-description">
            该主题包含 11 条回复，6个帖子，最后由
            <a class="bbp-author-avatar" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
             <img src="http://sdn.geekzu.org/avatar/f4c80de62e7291d5dd9a822c302b2013?s=14&amp;d=monsterid&amp;r=g"/>
            </a>
            <a class="bbp-author-name" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
             januslian
            </a>
            在
            <a href="http://cos.name/cn/topic/11207/#post-252219" title="回复：如何connect R to database然后计算?">
             7 年, 11 月 之前
            </a>
            更新。
           </p>
          </div>
          <div class="bbp-pagination">
           <div class="bbp-pagination-count">
            查看 12 个帖子 - 1 到 12（总计 12 个）
           </div>
           <div class="bbp-pagination-links">
           </div>
          </div>
          <ul class="forums bbp-replies" id="topic-11207-replies">
           <li class="bbp-header">
            <div class="bbp-reply-author">
             作者
            </div>
            <!-- .bbp-reply-author -->
            <div class="bbp-reply-content">
             帖子
            </div>
            <!-- .bbp-reply-content -->
           </li>
           <!-- .bbp-header -->
           <li class="bbp-body">
            <div class="bbp-reply-header" id="post-11207">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月12日 下午8:09
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-11207">
               1 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-11207 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-1 user-id-7484 topic-author post-11207 topic type-topic status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/7484/" rel="nofollow" title="查看karlqi的档案">
               <img src="http://sdn.geekzu.org/avatar/f5edfdb127d930ad1ca1450a7654dfb6?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/7484/" rel="nofollow" title="查看karlqi的档案">
               karlqi
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               《A Common Database Interface(DBI)》看不太明白，有没有人做过这类连接，有初级的范例吗？
               <br/>
               比方说，我想从Microsoft SQL Server的database中读一个表，在R中做一个比方说glm回归，输出结果，再调用glm.predict()新生成一列Y*，写到同一个database中，保存。如何做呢？
              </p>
              <p>
               内行人看来应该比较简单的吧。 还有就是R在处理database中的海量数据时是不是有什么限制呢？如何解决呢？在这点上SAS是怎么处理的呢？ 是不是通过数据库编程实现算法效率更高呢？ R / SAS是直接处理数据库中的数据呢，还是传过来再处理呢？
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251050">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月13日 上午8:24
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251050">
               2 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251050 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-2 user-id-21 post-251050 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               <img src="http://sdn.geekzu.org/avatar/f4c80de62e7291d5dd9a822c302b2013?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               januslian
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               不知道DBI能不能直接用，好像有针对专门数据库的具体实现，像RMySQL和RPgSQL。还有一个包是RODBC，
               <br/>
               记得Abel好像还专门写过一个教程，关于如何使用RODBC连接DBMS,并从中获取数据
               <br/>
               <a class="d4pbbc-url" href="http://www.magnsoftbi.com/forum/uploads/48/2006-10-31_101135_ROEBC2006-10-04_.pdf" rel="nofollow" target="_blank">
                http://www.magnsoftbi.com/forum/uploads/48/2006-10-31_101135_ROEBC2006-10-04_.pdf
               </a>
              </p>
              <p>
               我个人觉得用数据库并不能提高算法的效率，连接DBMS的好处在于当数据很大时，可以利用SQL对数据进行预处理，似乎R会把从数据库取出的数据储存在一个data.frame中，以后的分析是基于这样一个data frame进行的。我是这么理解的，不知道是不是有道理。
              </p>
              <p>
               说到效率，我倒有个现成的问题，我经常要对上市公司股价进行分析，常用的像事件研究法，
               <br/>
               就需要对发生同一类事件，比如兼并收购，的公司股价进行分析，计算异常收益，判断市场对事件做出什么反应。
               <br/>
               因为经常用到股价，所以我就使用PostgreSQL，把所有上市公司的股价统一导入一个表中，然后根据发生某一
               <br/>
               事件的公司代码和时间选择一定期间的股价数据，比如：
              </p>
              <pre class="highlight ">library(RODBC)
channel &lt;- odbcConnect("PostgreSQL30")
odbcGetInfo(channel)
      DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name 
    "PostgreSQL"          "8.3.1"          "03.00"   "PostgreSQL30" 
     Driver_Name       Driver_Ver         ODBC_Ver      Server_Name 
  "PSQLODBC.DLL"     "08.02.0500"     "03.52.0000"      "localhost" 
nrow(event)
    [1] 633
event[1:10,] ##数据的构成如下
   code   foredate
1     1 2008-01-18
2     2 2007-10-30
3     5 2007-10-29
4     7 2007-12-08
5     8 2008-01-03
6     9 2008-01-30
7    10 2007-10-31
8    11 2007-10-31
9    14 2008-01-29
10   16 2008-01-31
t1 &lt;- Sys.time()
ret1 &lt;- data.frame()
for(i in 1:nrow(event)) {
 ##取出price表中与event代码相同，foredate前171个交易日至foredate当天的收盘价数据
  query &lt;- paste("select code, date, close from price where code = ",
            event[i,1], " and date &lt;= '", event[i,2], "' order by date ",
              "desc limit 171", sep = "")
  tmp &lt;- sqlQuery(channel,query)
  ret1 &lt;- rbind(ret1, tmp)
}
</pre>
              <p>
               但是其速度奇慢无比，我从13：30看是运行这段程序，到现在还没得到结果呢，各位帮忙分析分析是什么原因？
               <br/>
               如果我把event直接作为一个表写入数据库，然后只通过SQL再取数据的时候，速度要快的多，不知道这是为什么？
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251195">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月16日 上午9:57
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251195">
               3 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251195 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-3 user-id-66286 post-251195 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/66286/" rel="nofollow" title="查看jianhe的档案">
               <img src="http://sdn.geekzu.org/avatar/ce4cacaed25985f4ba4f585705287947?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/66286/" rel="nofollow" title="查看jianhe的档案">
               jianhe
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               楼上的路子是对的，R对数据库的数据是先读过来，处理好，再写入数据库。大批量的数据处理分析还是得基于程序处理，即采用高级语言同时控制R及数据库
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251196">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月16日 上午10:00
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251196">
               4 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251196 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-4 user-id-66286 post-251196 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/66286/" rel="nofollow" title="查看jianhe的档案">
               <img src="http://sdn.geekzu.org/avatar/ce4cacaed25985f4ba4f585705287947?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/66286/" rel="nofollow" title="查看jianhe的档案">
               jianhe
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <blockquote class="d4pbbc-quote">
               <p>
                [b]引用第1楼[i]januslian[/i]于[i]2008-06-13 15:24[/i]发表的“”[/b]:
                <br/>
                不知道DBI能不能直接用，好像有针对专门数据库的具体实现，像RMySQL和RPgSQL。还有一个包是RODBC，
                <br/>
                记得Abel好像还专门写过一个教程，关于如何使用RODBC连接DBMS,并从中获取数据
                <br/>
                [url]http://www.magnsoftbi.com/forum/uploads/48/2006-10-31_101135_ROEBC2006-10-04_.pdf[/url]
               </p>
               <p>
                我个人觉得用数据库并不能提高算法的效率，连接DBMS的好处在于当数据很大时，可以利用SQL对数据进行预处理，似乎R会把从数据库取出的数据储存在一个data.frame中，以后的分析是基于这样一个data frame进行的。我是这么理解的，不知道是不是有道理。
                <br/>
                …….
               </p>
              </blockquote>
              <p>
               个人猜测，RODBC对于主流商业数据库的支持比较好，而对于PgSQL等数据库支持较差
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251235">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月17日 上午5:32
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251235">
               5 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251235 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-5 user-id-123 post-251235 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/123/" rel="nofollow" title="查看abel的档案">
               <img src="http://sdn.geekzu.org/avatar/60dea4c8a3f2115785ea7668a3654b2d?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/123/" rel="nofollow" title="查看abel的档案">
               abel
              </a>
              <br/>
              <div class="bbp-author-role">
               版主
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               这一行：ret1 &lt;- rbind(ret1, tmp)会让你的代码加速变慢
               <br/>
               nrow(event)大概是多少？如果是10~100级别的，还可以用用，如果是10000+的，就不要如此用了。
              </p>
              <p>
               我用SQLite等逐一做过类似的工作（原始数据存储+结果回写等），使用神经网络作预测，全部A股市场的股票来做，速度相当快。但是MS ACCESS似乎无论如何优化代码都还是慢字一个——搞计算机的人说这个软件是自己身设计上的缺陷，不过反正我最常用的是SQLite这个轻量级的小玩意。个人感觉，My和Pg无疑是非常非常好的dbms。
               <br/>
               在使用RODBC的connect的时候，最好用完就关掉，不要在循环中开太多的connect了，否则有内存开销的。有时候数据太大，俺就用gc来处理下。
              </p>
              <p>
               在怀疑RODBC等对啥玩意支持，还不如先分析下自己的代码和之上的设计思路呢。
              </p>
              <p>
               btw:貌似现在用R做金融的人多起来了阿，呵呵。好现象。
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251268">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月17日 下午1:20
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251268">
               6 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251268 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-6 user-id-7484 topic-author post-251268 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/7484/" rel="nofollow" title="查看karlqi的档案">
               <img src="http://sdn.geekzu.org/avatar/f5edfdb127d930ad1ca1450a7654dfb6?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/7484/" rel="nofollow" title="查看karlqi的档案">
               karlqi
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               谢谢指点
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251467">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月22日 上午3:42
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251467">
               7 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251467 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-7 user-id-21 post-251467 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               <img src="http://sdn.geekzu.org/avatar/f4c80de62e7291d5dd9a822c302b2013?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               januslian
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <blockquote class="d4pbbc-quote">
               <p>
                [b]引用第4楼[i]abel[/i]于[i]2008-06-17 12:32[/i]发表的“”[/b]:
                <br/>
                这一行：ret1 &lt;- rbind(ret1, tmp)会让你的代码加速变慢
               </p>
              </blockquote>
              <p>
               其实，后来我发现还不止这个问题。我的数据库是我自己建立的，本来没什么经验，后来才发现，如果在数据库中合理的使用索引，可以大大降低取数据的时间。比如
              </p>
              <pre class="highlight ">library(RODBC)
channle &lt;- odbcConnect("PostgreSQL30")
sqlQuery(channel, "drop index priceindex") ## 删除索引
t1 &lt;- Sys.time()
query1 &lt;- paste("select price.code, price.date,",
                "(price.close/price.closel-1) as ret, rm",
                "from price where price.code=",fore[1,1],
                "and price.date&lt;'", fore[1,2],"' order by date desc limit 150 offset 20",sep = " ")
tmp1 &lt;- sqlQuery(channel, query1)
t2 &lt;- Sys.time()
t2 - t1
##Time difference of 12.938 secs

##select语句中需要对code、date字段进行比较运算及排序，故根据code、date建立组合索引
t1 &lt;- Sys.time()
sqlQuery(channel, "create index priceindex on price (code,date)")
sqlQuery(channel, "cluster priceindex on price")
t2 &lt;- Sys.time()
t2 - t1 ##Time difference of 2.287133 mins


t1 &lt;- Sys.time()
query1 &lt;- paste("select price.code, price.date,",
                "(price.close/price.closel-1) as ret, rm",
                "from price where price.code=",fore[1,1],
                "and price.date&lt;'", fore[1,2],"' order by date desc limit 150 offset 20",sep = " ")
tmp1 &lt;- sqlQuery(channel, query1)
t2 &lt;- Sys.time()
t2 - t1 ##Time difference of 1.101 secs

odbcClose(channel)
</pre>
              <p>
               我总共要执行5551次查询，如果以每次12.938 secs计算的话，应该需要17个小时。而使用索引的请况下每次查询的时间只需要1 secs，缩短了不少，而建立索引的时间不过2分半钟（我的数据大概有300万条记录吧）。
               <br/>
               所以，在R中连接DBMS的时候，不仅要针对R code进行优化，而且要根据情况优化SQL查询语句，这样运行时间才能缩短。
              </p>
              <p>
               PS：是不是在win下长时间使用R会导致程序运行速度下降啊，今早起来刚开机的时候，运行一次查询只需要0.4秒，而现在却需要1.101 secs？
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251557">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月23日 下午12:04
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251557">
               8 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251557 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-8 user-id-336 post-251557 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/336/" rel="nofollow" title="查看刘思喆的档案">
               <img src="http://sdn.geekzu.org/avatar/6ca0529d22d1bf25a1e5cee3af3456f9?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/336/" rel="nofollow" title="查看刘思喆的档案">
               刘思喆
              </a>
              <br/>
              <div class="bbp-author-role">
               版主
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               这种情形下 主要是对数据库SQL语句的优化。
               <br/>
               以前见过 oracle 的工程师 就是专门搞语句优化的，年薪很高的
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251680">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月25日 下午1:43
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251680">
               9 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251680 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-9 user-id-3849 post-251680 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/3849/" rel="nofollow" title="查看anning189的档案">
               <img src="http://sdn.geekzu.org/avatar/f8d6415705583cd10615d52a1b6797db?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/3849/" rel="nofollow" title="查看anning189的档案">
               anning189
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               最近也在做类似的工作，数据500多M，循环读取数据库并回写处理结果，发现
               <br/>
               1、RSQLite非常慢，简直让人无法承受，不知道什么原因
               <br/>
               2、R读取Acess速度还可以，半个小时处理完成，如果是RSQLite大概要几天。
               <br/>
               R回写Acess时报错，‘C++ runtime error’之类的东西。
               <br/>
               不知道大家有没有遇到过
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251690">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月25日 下午5:03
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251690">
               10 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251690 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-10 user-id-123 post-251690 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/123/" rel="nofollow" title="查看abel的档案">
               <img src="http://sdn.geekzu.org/avatar/60dea4c8a3f2115785ea7668a3654b2d?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/123/" rel="nofollow" title="查看abel的档案">
               abel
              </a>
              <br/>
              <div class="bbp-author-role">
               版主
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               我还是蛮喜欢RSQlite这个小玩意的，怕麻烦就不想装其他东西了
               <br/>
               RSQLite使用的时候connect开了以后一定要关了，使其对称出现，哪怕这么做感觉有点麻烦——也许我可以看看你代码。要是实在是不行，还可以试一试RMySQL之类的。RSQLite在使用的时候，如果重复写入内容到一个表的时候，不管如何小心，偶尔会出现这个表的体积只大不小的情况；删除表中的某些记录后，还是保持那么大个的体积，这点可能还需要数据库方面的高人来处理下。
              </p>
              <p>
               Access使用的时候，经常莫名其妙导致Access自身变大的说，MS SQL Server这方面表现就好很多了。
              </p>
              <p>
               你每次查询要处理大概500M的数据，还是数据库一共这么大？
              </p>
              <p>
               关于ＳＱＬ设计＼优化等问题，那就已经有点超越R的范畴了，但是确实是相当重要的说！
              </p>
              <blockquote class="d4pbbc-quote">
               <p>
                [b]引用第8楼[i]anning189[/i]于[i]2008-06-25 20:43[/i]发表的“”[/b]:
                <br/>
                最近也在做类似的工作，数据500多M，循环读取数据库并回写处理结果，发现
                <br/>
                1、RSQLite非常慢，简直让人无法承受，不知道什么原因
                <br/>
                2、R读取Acess速度还可以，半个小时处理完成，如果是RSQLite大概要几天。
                <br/>
                R回写Acess时报错，‘C++ runtime error’之类的东西。
                <br/>
                不知道大家有没有遇到过
               </p>
              </blockquote>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-251699">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年6月26日 上午2:54
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-251699">
               11 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-251699 -->
            <div class="odd bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-11 user-id-3849 post-251699 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/3849/" rel="nofollow" title="查看anning189的档案">
               <img src="http://sdn.geekzu.org/avatar/f8d6415705583cd10615d52a1b6797db?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/3849/" rel="nofollow" title="查看anning189的档案">
               anning189
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               数据库500M,二层循环, 每次读取50行数据
              </p>
              <pre class="highlight ">m = dbDriver('SQLite')
con1 = dbConnect(m, dbname = 'highFrequencData_RSQLite.db')
con2 = dbConnect(m, dbname = 'highFrequencDataETFAribitrage_RSQLite.db')
for(i in 1:length(tradeDaySeries))
{
  cat('i = ', i, '\n')
  #获取当日清单中股票代码
  temStockCode = buySellList[buySellList[, '最新公告日'] == tradeDaySeries[i], '股票代码']
  for(j in 1:length(temStockCode))
  {
    #查询某个代码某日的数据
    temSQL = paste("select * ",
                   "from priceData ",
                   "where 代码 = ", temStockCode[j],
                   " and 时间 between '", tradeDaySeries[i], " 09:30' and '",tradeDaySeries[i], " 15:00'",
                   " order by 时间 asc", sep = '')
    
    rs = dbSendQuery(con1, temSQL)
    dat = fetch(rs, n = -1)
    dbClearResult(rs)
    #如果数据完整
    if(nrow(dat) == length(tradeTimeSeries)){
      dat = dat
    }
    #如果数据为空
    if(nrow(dat) == 0){
      sqlCode = paste("select * ",
                   "from priceData ",
                   "where 代码 = ", temStockCode[j],
                   " and 时间 &lt;= '", tradeDaySeries[i], " 15:00'",
                   " order by 时间 desc", sep = '')
      rsTingPai = dbSendQuery(con1, sqlCode)
     datTingPai = fetch(rsTingPai, n = 1)
      dbClearResult(rsTingPai)
      dat = data.frame(cbind(temStockCode[j], tradeTimeSeries, datTingPai[1, '收盘']))
      colnames(dat) = c('代码', '时间', '收盘')
    }
    #如果数据不完整，停牌1小时
    if(nrow(dat)&lt;length(tradeTimeSeries) &amp;&amp; nrow(dat)&gt;0){
      sqlCode = paste("select * ",
                   "from priceData ",
                   "where 代码 = ", temStockCode[j],
                   " and 时间 &lt;= '", tradeDaySeries[i-1], " 15:00'",
                   " order by 时间 desc", sep = '')
      rsOneHour = dbSendQuery(con1, sqlCode)
     datOneHour = fetch(rsTingPai, n = 1)
      dbClearResult(rsOneHour)
      tem = c(rep(datOneHour[1, '收盘'], length(tradeTimeSeries)-nrow(dat)),
              dat[, '收盘'])
      dat = data.frame(cbind(temStockCode[j], tradeTimeSeries, tem))
      colnames(dat) = c('代码', '时间', '收盘')
     }
    #将结果写到数据库
    dbWriteTable(con2, 'priceData', dat, row.names = F,
                 overwrite = F, append = T)
  }
}
dbDisconnect(con1)
dbDisconnect(con2)</pre>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
            <div class="bbp-reply-header" id="post-252219">
             <div class="bbp-meta">
              <span class="bbp-reply-post-date">
               2008年7月7日 上午11:13
              </span>
              <a class="bbp-reply-permalink" href="http://cos.name/cn/topic/11207/#post-252219">
               12 楼
              </a>
              <span class="bbp-admin-links">
              </span>
             </div>
             <!-- .bbp-meta -->
            </div>
            <!-- #post-252219 -->
            <div class="even bbp-parent-forum-999 bbp-parent-topic-11207 bbp-reply-position-12 user-id-21 post-252219 reply type-reply status-publish hentry">
             <div class="bbp-reply-author">
              <a class="bbp-author-avatar" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               <img src="http://sdn.geekzu.org/avatar/f4c80de62e7291d5dd9a822c302b2013?s=80&amp;d=monsterid&amp;r=g"/>
              </a>
              <br/>
              <a class="bbp-author-name" href="http://cos.name/cn/profile/21/" rel="nofollow" title="查看januslian的档案">
               januslian
              </a>
              <br/>
              <div class="bbp-author-role">
               普通会员
              </div>
             </div>
             <!-- .bbp-reply-author -->
             <div class="bbp-reply-content">
              <p>
               看一下这个，说不定对你有用。
              </p>
              <p>
               Re: [R-sig-DB] dbSendPreparedQuery in RSQLite
               <br/>
               发件人： "Sean Davis" &lt;sdavis2@mail.nih.gov&gt;
               <br/>
               收件人：    "James Bullard" &lt;bullard@berkeley.edu&gt;
               <br/>
               抄送人：    r-sig-db@stat.math.ethz.ch
               <br/>
               日　期：     2008-04-16 23:47:07
              </p>
              <p>
               On Tue, Apr 15, 2008 at 11:45 PM, James Bullard  wrote:
               <br/>
               &gt; Yes, in this example that would certainly work. However, I have a large
               <br/>
               &gt; number of queries in a tight loop and it would be nice to avoid the overhead
               <br/>
               &gt; of the query parsing and compiling on each call through the loop.
              </p>
              <p>
               A couple of typical tricks for avoidance of loops is to either:
              </p>
              <p>
               1) Use an "in" query
               <br/>
               2) Use a temporary table (CREATE TEMPORARY TABLE …. or
               <br/>
               dbWriteTable()) and then do a join
              </p>
              <p>
               If you have hundreds or thousands of queries, it is quite possible
               <br/>
               that #2 could be much faster.  Benchmarking would be useful here, but
               <br/>
               it is definitely worth a try if you are too slow with looping.
              </p>
              <p>
               Sean
              </p>
              <p>
               Re: [R-sig-DB] dbSendPreparedQuery in RSQLite
               <br/>
               发件人：
               <br/>
               "Sean Davis" &lt;sdavis2@mail.nih.gov&gt;
               <br/>
               收件人：    "James Bullard" &lt;bullard@berkeley.edu&gt;
               <br/>
               抄送人：    r-sig-db@stat.math.ethz.ch
               <br/>
               日　期：     2008-04-17 03:21:43
               <br/>
               On Wed, Apr 16, 2008 at 1:35 PM, James Bullard  wrote:
               <br/>
               &gt; Okay, well I thought that what I was doing was not easily done with one
               <br/>
               &gt; query but since I am wrong more often than not I'll ask and see if anyone
               <br/>
               &gt; can think of a way to combine it into one query.
               <br/>
               &gt;
               <br/>
               &gt;  I have a set of regions:
               <br/>
               &gt;
               <br/>
               &gt;  regions &lt;- cbind(chr = c(1,2,3), start = c(1,230,4950), end = c(293, 320,
               <br/>
               &gt; 19200))
               <br/>
               &gt;
               <br/>
               &gt;  In the db I have the following table:
               <br/>
               &gt;
               <br/>
               &gt;  CREATE TABLE count_table (chr, location, count);
               <br/>
               &gt;
               <br/>
               &gt;  chr location count
               <br/>
               &gt;  1       10              1
               <br/>
               &gt;  1       290             10
               <br/>
               &gt;  2       245             3
               <br/>
               &gt;  3       10              2
               <br/>
               &gt;
               <br/>
               &gt;  The query that I am using is:
               <br/>
               &gt;
               <br/>
               &gt;  apply(regions, 1, function(region) {
               <br/>
               &gt;         paste("SELECT * FROM count_table WHERE chr =", region[1], "AND
               <br/>
               &gt; location &gt;=", region[2],  "AND location &lt;=", region[3], ";")
               <br/>
               &gt;  })
               <br/>
               &gt;
               <br/>
               &gt;  Which gives me back the following (which is exactly what I want):
               <br/>
               &gt;  [[1]]
               <br/>
               &gt;   chr location count__1
               <br/>
               &gt;  1   1       10        1
               <br/>
               &gt;  2   1      290       10
               <br/>
               &gt;
               <br/>
               &gt;  [[2]]
               <br/>
               &gt;   chr location count__1
               <br/>
               &gt;  1   2      245        3
               <br/>
               &gt;
               <br/>
               &gt;  [[3]]
               <br/>
               &gt;  NULL data frame with 0 rows
               <br/>
               &gt;
               <br/>
               &gt;
               <br/>
               &gt;  So I don't see any obvious way to pass in multiple ranges, but maybe I
               <br/>
               &gt; missing something with using the IN operator, thanks in advance, sorry for
               <br/>
               &gt; the long email.
              </p>
              <p>
               &gt; counts &lt;- data.frame(chr=c(1,1,2,3),location=c(10,290,245,10),count=c(1,10,3,2))
               <br/>
               &gt; dbWriteTable(con,'count_table',counts,row.names=FALSE,overwrite=TRUE)
               <br/>
               &gt; dbGetQuery(con,'select * from count_table')
               <br/>
               chr location count__1
               <br/>
               1   1       10        1
               <br/>
               2   1      290       10
               <br/>
               3   2      245        3
               <br/>
               4   3       10        2
              </p>
              <p>
               &gt; regions &lt;- data.frame(chr = c(1,2,3), start = c(1,230,4950), end = c(293, 320, 19200))
               <br/>
               &gt; dbWriteTable(con,'regions',regions,row.names=FALSE,overwrite=TRUE)
               <br/>
               &gt; dbGetQuery(con,'select * from regions')
               <br/>
               chr start end__1
               <br/>
               1   1     1    293
               <br/>
               2   2   230    320
               <br/>
               3   3  4950  19200
              </p>
              <p>
               &gt; dbGetQuery(con,'select count_table.* from count_table join regions on regions.chr=count_table.chr and count_table.location between regions.start and regions.end__1')
               <br/>
               chr location count__1
               <br/>
               1   1       10        1
               <br/>
               2   1      290       10
               <br/>
               3   2      245        3
              </p>
              <p>
               So, the principle of a temp table works.  Let's try some benchmarking.
               <br/>
               Create a random count table of length 100k rows, write it to a
               <br/>
               database and create some indexes to speed querying.
              </p>
              <p>
               &gt; counts &lt;- data.frame(chr=sample(1:22,100000,replace=TRUE),location=sample(1:100000000,100000,replace=TRUE),count=sample(1:10,100000,replace=TRUE))
               <br/>
               &gt; counts[1:5,]
               <br/>
               chr location count
               <br/>
               1  14 91737974     5
               <br/>
               2   5 61059218     2
               <br/>
               3   2 23944824     4
               <br/>
               4   3 82907389     9
               <br/>
               5  22 94658940     1
               <br/>
               &gt; dbWriteTable(con,'count_table',counts,row.names=FALSE,overwrite=TRUE)
               <br/>
               [1] TRUE
               <br/>
               &gt; dbGetQuery(con,'create index ct_chr on count_table(chr)')
               <br/>
               NULL
               <br/>
               &gt; dbGetQuery(con,'create index ct_location on count_table(location)')
               <br/>
               NULL
              </p>
              <p>
               So, to try a benchmark, I wrote a little function that creates random
               <br/>
               regions (n of them, see function below).  In the tmp table approach,
               <br/>
               the data.frame is written to a table in the database and a join is
               <br/>
               used.  In the second, the apply() approach is used.  The function
               <br/>
               below returns the system.time for each approach.  In my quick
               <br/>
               benchmarking, the temp table approach is about 5 times faster
               <br/>
               (including writing the temp table) with a count_table of length 100k
               <br/>
               rows.  I would expect that it might be a very much larger difference
               <br/>
               as the size of the count_table increases, as the indexes on
               <br/>
               count_table must be scanned for each of the apply queries and only
               <br/>
               once for the temp table/join approach.
              </p>
              <p>
               benchRegions &lt;- function(n) {
               <br/>
               # n is the number of regions.
               <br/>
               # returns a list with 2 sets of system times, one for
               <br/>
               #  the temp table approach, and one for the apply approach
               <br/>
               res &lt;- list()
               <br/>
               starts=sample(1:100000000,n,replace=TRUE)
               <br/>
               ends=starts+sample(1:1000,n,replace=TRUE)
               <br/>
               regions &lt;- data.frame(chr=sample(1:22,n,replace=TRUE),
               <br/>
               start=starts,
               <br/>
               end=ends)
               <br/>
               res[['tmptab']] &lt;-
               <br/>
               system.time({dbWriteTable(con,'regions',regions,row.names=FALSE,overwrite=TRUE);
               <br/>
               dbGetQuery(con,'select count_table.* from count_table join regions on
               <br/>
               regions.chr=count_table.chr and count_table.location between
               <br/>
               regions.start and regions.end__1')})
               <br/>
               res[['apply']] &lt;- system.time(apply(regions, 1, function(region)
               <br/>
               {dbGetQuery(con,paste("SELECT * FROM count_table WHERE chr =",
               <br/>
               region[1], "AND location &gt;=", region[2],  "AND location &lt;=",
               <br/>
               region[3], ";"))
               <br/>
               }))
               <br/>
               return(res)
               <br/>
               }
              </p>
              <p>
               Hope that helps.
              </p>
              <p>
               Sean
              </p>
             </div>
             <!-- .bbp-reply-content -->
            </div>
            <!-- .reply -->
           </li>
           <!-- .bbp-body -->
           <li class="bbp-footer">
            <div class="bbp-reply-author">
             作者
            </div>
            <div class="bbp-reply-content">
             帖子
            </div>
            <!-- .bbp-reply-content -->
           </li>
           <!-- .bbp-footer -->
          </ul>
          <!-- #topic-11207-replies -->
          <div class="bbp-pagination">
           <div class="bbp-pagination-count">
            查看 12 个帖子 - 1 到 12（总计 12 个）
           </div>
           <div class="bbp-pagination-links">
           </div>
          </div>
          <div class="bbp-no-reply" id="no-reply-11207">
           <div class="bbp-template-notice">
            <p>
             您必须先登录才能回复该主题。
            </p>
           </div>
          </div>
         </div>
        </div>
        <!-- .entry-content -->
        <footer class="entry-meta">
        </footer>
        <!-- .entry-meta -->
       </article>
       <!-- #post -->
       <div class="comments-area" id="comments">
       </div>
       <!-- #comments -->
      </div>
      <!-- #content -->
     </div>
     <!-- #primary -->
     <div class="sidebar-container" id="tertiary" role="complementary">
      <div class="sidebar-inner">
       <div class="widget-area">
        <aside class="widget bbp_widget_login" id="bbp_login_widget-2">
         <h3 class="widget-title">
          登录
         </h3>
         <form action="http://cos.name/cn/wp-login.php" class="bbp-login-form" method="post">
          <fieldset>
           <legend>
            登录
           </legend>
           <div class="bbp-username">
            <label for="user_login">
             用户名:
            </label>
           </div>
           <div class="bbp-password">
            <label for="user_pass">
             密码:
            </label>
           </div>
           <div class="bbp-remember-me">
            <label for="rememberme">
             记住用户名
            </label>
           </div>
           <div class="bbp-submit-wrapper">
            <button class="button submit user-submit" id="user-submit" name="user-submit" tabindex="104" type="submit">
             登录
            </button>
           </div>
           <div class="bbp-login-links">
            <a class="bbp-register-link" href="http://cos.name/cn/wp-login.php?action=register" title="注册">
             注册
            </a>
            <a class="bbp-lostpass-link" href="http://cos.name/cn/wp-login.php?action=lostpassword" title="忘记密码">
             忘记密码
            </a>
           </div>
          </fieldset>
         </form>
        </aside>
        <aside class="widget widget_text" id="text-7">
         <h3 class="widget-title">
          搜索
         </h3>
         <div class="textwidget">
          <form action="http://www.google.com/search" id="bbp-search-form" method="get" onsubmit="Gsitesearch(this)" role="search">
           <div>
           </div>
          </form>
          <form id="bbp-search-form-baidu" onsubmit="g(this)" role="search">
           <div>
           </div>
          </form>
         </div>
        </aside>
        <aside class="widget widget_text" id="text-2">
         <h3 class="widget-title">
          新鲜事
         </h3>
         <div class="textwidget">
          <ul>
           <li>
            <a href="http://cos.name/cn/topics/">
             最新帖子
            </a>
           </li>
           <li>
            <a href="http://cos.name/cn/view/popular/">
             最热门主题
            </a>
           </li>
           <li>
            <a href="http://cos.name/cn/view/no-replies/">
             消灭零回复
            </a>
           </li>
          </ul>
         </div>
        </aside>
        <aside class="widget widget_text" id="text-3">
         <h3 class="widget-title">
          RSS订阅
         </h3>
         <div class="textwidget">
          <ul>
           <li>
            <img src="http://cos.name/wp-includes/images/rss.png"/>
            <a href="http://cos.name/cn/topics/feed/">
             所有主题
            </a>
           </li>
           <li>
            <img src="http://cos.name/wp-includes/images/rss.png"/>
            <a href="http://cos.name/cn/forums/feed/">
             所有帖子
            </a>
           </li>
          </ul>
         </div>
        </aside>
       </div>
       <!-- .widget-area -->
      </div>
      <!-- .sidebar-inner -->
     </div>
     <!-- #tertiary -->
    </div>
    <!-- #main -->
    <footer class="site-footer" id="colophon" role="contentinfo">
     <div class="site-info">
      版权所有 © 2014 统计之都 | 由
      <a href="http://wordpress.org/">
       WordPress
      </a>
      构建 | 主题修改自
      <a href="http://wordpress.org/themes/twentythirteen">
       Twenty Thirteen
      </a>
     </div>
     <!-- .site-info -->
    </footer>
    <!-- #colophon -->
   </div>
   <!-- #page -->
  </div>
 </body>
</html>